package com.sxfq.loansupermarket.backsystem.modular.marketing.controller;

import com.baomidou.mybatisplus.plugins.Page;
import com.sxfq.loansupermarket.backsystem.common.constant.factory.PageFactory;
import com.sxfq.loansupermarket.backsystem.common.controller.BaseController;
import com.sxfq.loansupermarket.backsystem.common.exception.BizExceptionEnum;
import com.sxfq.loansupermarket.backsystem.common.exception.BussinessException;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.User;
import com.sxfq.loansupermarket.backsystem.core.log.LogManager;
import com.sxfq.loansupermarket.backsystem.core.log.factory.LogTaskFactory;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.support.HttpKit;
import com.sxfq.loansupermarket.backsystem.core.util.DateUtil;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.core.util.ToolUtil;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.CustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.*;
import com.sxfq.loansupermarket.backsystem.modular.basicData.service.IGetListService;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.ReservationsInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.SearchReservationsInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.TongJiInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.service.IReservationsArrivedMoneyService;
import com.sxfq.loansupermarket.backsystem.modular.marketing.service.IReservationsService;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.AddVisitedInfo;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.ConsultantInfo;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.DepartmentInfo;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.service.IConsultantService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author py
 * @Date
 */
@Controller
@RequestMapping("/reservationsArriveMoney")
public class ReservationsArrivedMoneyController extends BaseController {
    @Autowired
    IReservationsArrivedMoneyService reservationsArrivedMoneyService;
    @Autowired
    private RedisTemplate redisTemplate;
    @Autowired
    IReservationsService reservationsService;

    @Autowired
    IGetListService getListService;

    @Autowired
    IConsultantService service;

    @Autowired
    private SqlMapper sqlMapper;

    private String PREFIX = "/marketing/reservations/";

    /**
     * 市场部预约管理 已到 列表
     */
    @RequestMapping("/listMoneyTotal")
    @ResponseBody
    public Object listYD(SearchReservationsInfo info, HttpServletRequest request) {
        Page<ReservationsInfo> page = new PageFactory<ReservationsInfo>().defaultPage();
        try {
            info.setStartIndex(page.getSize() * (page.getCurrent() - 1));
            info.setLimitIndex(page.getSize());
            page.setCurrent(0);
            List<ReservationsInfo> list = reservationsArrivedMoneyService.selectPageListByStatusMapMoney(page, info);
            if (list.size() == 0) {
                info.setStartIndex(0);
                list = reservationsArrivedMoneyService.selectPageListByStatusMapMoney(page, info);
            }
            page.setRecords(list);
            page.setTotal(reservationsArrivedMoneyService.getQueryPagesCountMoney(page, info));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return super.packForBT(page);
    }


    //已到导出
    @RequestMapping(value = "/reservationsExportMoneyTotal")
    public void reservationsExportYD(@Valid SearchReservationsInfo searchInfo, HttpServletResponse response) {
        try {
            List<ReservationsInfo> list = reservationsArrivedMoneyService.reservationsExportMoney(searchInfo);
            // 解析成excel文件
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在wb中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("前端导出");
            for (int i = 0; i < 31; i++) {
                sheet.setColumnWidth(i, 24 * 256);
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("预约号");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue("标识号");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("姓名");
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue("手机号码");
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue("初诊门诊");
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell.setCellValue("建档人");
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue("跟进人");
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue("归属人");
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue("建档时间");
            cell.setCellStyle(style);
            cell = row.createCell(9);
            cell.setCellValue("预约时间");
            cell.setCellStyle(style);
            cell = row.createCell(10);
            cell.setCellValue("回访状态");
            cell.setCellStyle(style);
            cell = row.createCell(11);
            cell.setCellValue("到诊时间");
            cell.setCellStyle(style);
            cell = row.createCell(12);
            cell.setCellValue("结账时间");
            cell.setCellStyle(style);
            cell = row.createCell(13);
            cell.setCellValue("结账门诊");
            cell.setCellStyle(style);
            cell = row.createCell(14);
            cell.setCellValue("一级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(15);
            cell.setCellValue("二级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(16);
            cell.setCellValue("三级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(17);
            cell.setCellValue("回访");
            cell.setCellStyle(style);
            cell = row.createCell(18);
            cell.setCellValue("性别");
            cell.setCellStyle(style);
            cell = row.createCell(19);
            cell.setCellValue("年龄");
            cell.setCellStyle(style);
            cell = row.createCell(20);
            cell.setCellValue("成交状态");
            cell.setCellStyle(style);
            cell = row.createCell(21);
            cell.setCellValue("成交金额");
            cell.setCellStyle(style);
            cell = row.createCell(22);
            cell.setCellValue("预约病种");
            cell.setCellStyle(style);
            cell = row.createCell(23);
            cell.setCellValue("访问病种");
            cell.setCellStyle(style);
            cell = row.createCell(24);
            cell.setCellValue("消费项目");
            cell.setCellStyle(style);
            cell = row.createCell(25);
            cell.setCellValue("初诊TC");
            cell.setCellStyle(style);
            cell = row.createCell(26);
            cell.setCellValue("关键词");
            cell.setCellStyle(style);
            cell = row.createCell(27);
            cell.setCellValue("微信号");
            cell.setCellStyle(style);

            // 填充数据
            for (int i = 0; i < list.size(); i++) {
                ReservationsInfo map = list.get(i);
                row = sheet.createRow(i + 1);
                String reservationsNum = map.getReservationsNum();
                // 第四步，创建单元格，并设置值
                for (int j = 0; j <= 27; j++) {
                    row.createCell(j).setCellStyle(style);
                }
                row.getCell(0).setCellValue(map.getReservationsNum());
                row.getCell(1).setCellValue(map.getIdentificationNumber());
                row.getCell(2).setCellValue(map.getCustomerName());
                if (Integer.parseInt(redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"PhonePrivilegesexp",0)+"") == 1) {
                    row.getCell(3).setCellValue(map.getCustomerPhone());
                } else {
                    row.getCell(3).setCellValue("无");
                }
                row.getCell(4).setCellValue(map.getArrivalStore());
                row.getCell(5).setCellValue(map.getArchiving());
                row.getCell(6).setCellValue(map.getFollower());
                row.getCell(7).setCellValue(map.getBelonger());
                row.getCell(8).setCellValue(map.getFilingTime());
                row.getCell(9).setCellValue(map.getAppointmentTime());
                row.getCell(10).setCellValue(map.getStatuss());
                row.getCell(11).setCellValue(map.getFirstVisitDate());
                row.getCell(12).setCellValue(map.getTransactionTime());
                row.getCell(13).setCellValue(map.getOutpatientNamejz());
                row.getCell(14).setCellValue(map.getOneChannel());
                row.getCell(15).setCellValue(map.getTwoChannel());
                row.getCell(16).setCellValue(map.getThrChannel());
                row.getCell(17).setCellValue(map.getHuifang());
                row.getCell(18).setCellValue(map.getSex());
                row.getCell(19).setCellValue(map.getCustomerAge());
                row.getCell(20).setCellValue(map.getDealStatus());
                row.getCell(21).setCellValue(map.getMoney());
                row.getCell(22).setCellValue(map.getAppointmentPayService());
                row.getCell(23).setCellValue(map.getAccessDisease());
                row.getCell(24).setCellValue(map.getExpenditureProject());
                row.getCell(25).setCellValue(map.getTcName());
                row.getCell(26).setCellValue(map.getKeyWord());
                row.getCell(27).setCellValue(map.getWechatNumber());
            }
            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new Date());
            String filename = "data" + date + ".xls";
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 统计查询
     */
    @RequestMapping(value = "/tongjiMoneyTotal", method = RequestMethod.GET)
    @ResponseBody
    public List<TongJiInfo> change(String param1, String param2, String param3, String param4, String param5, String param6, String param7, String param8,
                                   String param9, String param10, String param11, String param12, String param13, String param14, String param15,
                                   String param16, String param17) throws Exception {
        //1.获取当前日期，格式为yyyy-MM-dd hh:mm:ss
        String currentDate = DateUtil.getTime(new Date()).substring(0, 10);//取今天的年月日
        //获取昨天日期字符串 格式为yyyy-MM-dd hh:mm:ss
        String lastDate = DateUtil.getAfterDayDate("-1").substring(0, 10);//取昨日 的年月日
        //获取当月的字符串
        String currerntMonth = currentDate.substring(0, 7);//yyyy-mm
        //获取上月日期
        String lastMonth = DateUtil.getAfterDayMonth("-1").substring(0, 7);//取上月的年月
        String contitionInfo = "";
        if (StringUtil.isNotEmpty(param1) && !"请选择".equals(param1)) {
            String arrivalStore = "";
            String arrivalStore2[] = param1.split(",");
            if (arrivalStore2.length == 1) {
                arrivalStore = arrivalStore2[0];
            } else {
                for (int i = 0; i < arrivalStore2.length; i++) {
                    if (i == (arrivalStore2.length - 1)) {
                        arrivalStore += "'" + arrivalStore2[i];
                    } else if (i == 0) {
                        arrivalStore += arrivalStore2[i] + "'" + ",";
                    } else {
                        arrivalStore += "'" + arrivalStore2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.arrival_store in ( '" + arrivalStore + "') ";
        }
        if (StringUtils.isNotBlank(param2)) {
            contitionInfo += " and t.follower in (select t1.account from user t1 where  t1.roleid='" + param2 + "') ";
        }
        if (StringUtils.isNotBlank(param3)) {
            contitionInfo += " and t.appointment_pay_service= '" + param3 + "' ";
        }
        if (StringUtils.isNotBlank(param4)) {
            contitionInfo += " and t.archiving= '" + param4 + "' ";
        }
        if (StringUtils.isNotBlank(param5)) {
            contitionInfo += " and t.follower= '" + param5 + "' ";
        }
        if (StringUtils.isNotBlank(param6)) {
            contitionInfo += " and t.belonger= '" + param6 + "' ";
        }
        if (StringUtils.isNotBlank(param7)) {
            contitionInfo += " and t.access_disease= '" + param7 + "' ";
        }
        if (StringUtils.isNotBlank(param8)) {
            if (!"请选择".equals(param8)) {
                contitionInfo += " and t.one_channel= '" + param8 + "' ";
            }
        }
        if (StringUtils.isNotBlank(param9)) {
            if (!"请选择".equals(param9)) {
                contitionInfo += " and t.two_channel= '" + param9 + "' ";
            }
        }
        if (StringUtils.isNotBlank(param10)) {
            if (!"请选择".equals(param10)) {
                contitionInfo += " and t.thr_channel= '" + param10 + "' ";
            }
        }
        if (StringUtil.isNotEmpty(param11) && !"请选择".equals(param11)) {
            String statuss = "";
            String statuss2[] = param11.split(",");
            if (statuss2.length == 1) {
                statuss = statuss2[0];
            } else {
                for (int i = 0; i < statuss2.length; i++) {
                    if (i == (statuss2.length - 1)) {
                        statuss += "'" + statuss2[i];
                    } else if (i == 0) {
                        statuss += statuss2[i] + "'" + ",";
                    } else {
                        statuss += "'" + statuss2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.statuss in ( '" + statuss + "') ";
        }
        if (StringUtil.isNotEmpty(param14) && !"请选择".equals(param14)) {
            String area = "";
            String area2[] = param14.split(",");
            if (area2.length == 1) {
                area = area2[0];
            } else {
                for (int i = 0; i < area2.length; i++) {
                    if (i == (area2.length - 1)) {
                        area += "'" + area2[i];
                    } else if (i == 0) {
                        area += area2[i] + "'" + ",";
                    } else {
                        area += "'" + area2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.arrival_store in (select a.simplename from dept a where a.area_abbreviation in ('" + area + "')) ";
        }
        if (StringUtils.isNotBlank(param16)) { //结账门诊
            contitionInfo += " and t1.outpatient_name= '" + param16 + "' ";
        }
        if (StringUtils.isNotBlank(param17)) {   //成交状态
            if ("已成交".equals(param17)) {
                contitionInfo += " and t1.transaction_amount is not null";
            }
            if ("未成交".equals(param17)) {
                contitionInfo += " and t1.transaction_amount is null";
            }
        }
        //姓名   电话   微信  预约号
        if (StringUtils.isNotBlank(param15)) {
            contitionInfo += " and (t.customer_name like CONCAT('%" + param15 + "%') or t.customer_phone like CONCAT('%" + param15 + "%') " +
                    " or t.customer_phone2 like CONCAT('%" + param15 + "%') " +
                    " or t.wechat_number like CONCAT('%" + param15 + "%') or t.reservations_num like CONCAT('%" + param15 + "%') " +
                    " or t.identification_number like CONCAT('%" + param15 + "%') or t.key_word like CONCAT('%" + param15 + "%') " +
                    " or t.remarks like CONCAT('%" + param15 + "%') or t.huifang like CONCAT('%" + param15 + "%') " +
                    " or t.szm like CONCAT('%" + param15 + "%')) ";
        }
        contitionInfo += " group by t1.reservations_id ) t ";
        //查询今日结账
        String sql = "select count(*) as countNum from (select t.id from reservations_infosc t right join reservations_charge_record_info t1 on t.id=t1.reservations_id " +
                " where left(transaction_time,10) = '" + currentDate + "' and t.status ='到店' " + contitionInfo +
                //查询昨日结账
                " union all select count(*) as countNum from (select t.id from reservations_infosc t right join reservations_charge_record_info t1 on t.id=t1.reservations_id" +
                " where left(transaction_time,10) = '" + lastDate + "' and t.status ='到店' " + contitionInfo +
                //查询本周结账
                " union all select count(*) as countNum from (select t.id from reservations_infosc t right join reservations_charge_record_info t1 on t.id=t1.reservations_id" +
                " where transaction_time >='" + param12 + "' and transaction_time <='" + param13 + "' and t.status ='到店' " + contitionInfo +
                //查询当月结账
                " union all select count(*) as countNum from (select t.id from reservations_infosc t right join reservations_charge_record_info t1 on t.id=t1.reservations_id " +
                " where left(transaction_time,7) = '" + currerntMonth + "' and t.status ='到店' " + contitionInfo +
                //查询上月结账
                " union all select count(*) as countNum from (select t.id from reservations_infosc t right join reservations_charge_record_info t1 on t.id=t1.reservations_id " +
                " where left(transaction_time,7) = '" + lastMonth + "' and t.status ='到店' " + contitionInfo;
        List<TongJiInfo> tongJiInfoList = sqlMapper.selectList(sql, TongJiInfo.class);
        return tongJiInfoList;
    }

    @RequestMapping("/accountMoneyTotal")
    @ResponseBody
    public HashMap sumAmount(@RequestParam(required = false) String arrivalStore,
                             @RequestParam(required = false) String rechargeStore,
                             @RequestParam(required = false) String dealStatus,
                             @RequestParam(required = false) String department,
                             @RequestParam(required = false) String reach,
                             @RequestParam(required = false) String area,
                             @RequestParam(required = false) String appointmentPayService,
                             @RequestParam(required = false) String archiving,
                             @RequestParam(required = false) String follower,
                             @RequestParam(required = false) String accessDisease,
                             @RequestParam(required = false) String oneChannel, @RequestParam(required = false) String twoChannel,
                             @RequestParam(required = false) String thrChannel, @RequestParam(required = false) String statuss,
                             @RequestParam(required = false) String status,
                             @RequestParam(required = false) String startTransactionTime, @RequestParam(required = false) String endTransactionTime,
                             @RequestParam(required = false) String belonger, HttpServletResponse response) {
        String sql = "select sum(t1.transaction_amount) as totalMoney from reservations_infosc t right join reservations_charge_record_info t1 on t.id=t1.reservations_id where 1=1";
        if (StringUtil.isNotEmpty(reach)) {
            sql += " and (t.customer_name like CONCAT('%','" + reach + "','%') or t.customer_phone like CONCAT('%','" + reach + "','%') or t.wechat_number like CONCAT('%','" + reach + "','%') or t.reservations_num like CONCAT('%','" + reach + "','%') or t.szm like CONCAT('%','" + reach + "','%') or t.customer_phone2 like CONCAT('%','" + reach + "','%')) ";
        }
        if (StringUtil.isNotEmpty(appointmentPayService)) {
            sql += " and t.appointment_pay_service = '" + appointmentPayService + "'";
        }
        if (StringUtil.isNotEmpty(area) && !"请选择".equals(area)) {
            String area1 = "";
            String area2[] = area.split(",");
            if (area2.length == 1) {
                area1 = area2[0];
            } else {
                for (int i = 0; i < area2.length; i++) {
                    if (i == (area2.length - 1)) {
                        area1 += "'" + area2[i];
                    } else if (i == 0) {
                        area1 += area2[i] + "'" + ",";
                    } else {
                        area1 += "'" + area2[i] + "'" + ",";
                    }
                }
            }
            sql += " and t.arrival_store in (select a.simplename from dept a where a.area_abbreviation in ('" + area1 + "')) ";
        }
        if (StringUtil.isNotEmpty(arrivalStore)) {
            String arrivalStores = "";
            String arrivalStore2[] = arrivalStore.split(",");
            if (arrivalStore2.length == 1) {
                arrivalStores = arrivalStore2[0];
            } else {
                for (int i = 0; i < arrivalStore2.length; i++) {
                    if (i == (arrivalStore2.length - 1)) {
                        arrivalStores += "'" + arrivalStore2[i];
                    } else if (i == 0) {
                        arrivalStores += arrivalStore2[i] + "'" + ",";
                    } else {
                        arrivalStores += "'" + arrivalStore2[i] + "'" + ",";
                    }
                }
            }
            sql += " and t.arrival_store in( '" + arrivalStores + "')";
        }
        if (StringUtil.isNotEmpty(follower)) {
            sql += " and t.follower = '" + follower + "'";
        }
        if (StringUtil.isNotEmpty(belonger)) {
            sql += " and t.belonger = '" + belonger + "'";
        }
        if (StringUtil.isNotEmpty(archiving)) {
            sql += " and t.archiving = '" + archiving + "'";
        }
        if (StringUtil.isNotEmpty(accessDisease)) {
            sql += " and t.access_disease = '" + accessDisease + "'";
        }
        if (StringUtil.isNotEmpty(startTransactionTime)) {
            sql += " and t1.transaction_time  >= '" + startTransactionTime + "'";
        }
        if (StringUtil.isNotEmpty(endTransactionTime)) {
            sql += " and t1.transaction_time  <= '" + endTransactionTime + "'";
        }
        if (StringUtil.isNotEmpty(statuss)) {
            sql += " and t.statuss  = '" + statuss + "'";
        }
        if (StringUtil.isNotEmpty(oneChannel) && !oneChannel.equals("请选择")) {
            sql += " and t.one_channel  = '" + oneChannel + "'";
        }
        if (StringUtil.isNotEmpty(twoChannel) && !twoChannel.equals("请选择")) {
            sql += " and t.two_channel  = '" + twoChannel + "'";
        }
        if (StringUtil.isNotEmpty(thrChannel) && !thrChannel.equals("请选择")) {
            sql += " and t.thr_channel  = '" + thrChannel + "'";
        }
        if (StringUtil.isNotEmpty(department)) {
            sql += " and t.follower in (select t1.account from user t1 where  t1.roleid='" + department + "') ";

        }
        if (StringUtil.isNotEmpty(status)) {
            sql += " and t.status  = '" + status + "'";
        }
        if (StringUtils.isNotBlank(rechargeStore)) { //结账门诊
            sql += " and t1.outpatient_name= '" + rechargeStore + "' ";
        }
        if (StringUtils.isNotBlank(dealStatus)) {   //成交状态
            if ("已成交".equals(dealStatus)) {
                sql += " and t1.transaction_amount is not null";
            }
            if ("未成交".equals(dealStatus)) {
                sql += " and t1.transaction_amount is null";
            }
        }
        Map<String, Object> result = sqlMapper.selectOne(sql.toString());
        HashMap map = new HashMap();
        if (result != null) {
            map.put("totalMoney", result.get("totalMoney"));
        } else {
            map.put("totalMoney", "0");
        }
        return map;
    }

    /**
     * 已到 点击姓名跳转客户管理详情页面  （需要获取到成交记录   和  看诊记录的数据）
     */
    @RequestMapping(value = "/reservations_arrives_moneyTotal/{id}")
    public String to_reservationsNotDetails(@PathVariable String id, Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ToolUtil.isEmpty(id)) {
            return "/errorClose.html";
        }
        try {
            List<ConsultantInfo> consultantInfoList = service.getConsultantInfoList(id);
            if (consultantInfoList.size() > 0) {
                for (ConsultantInfo infos : consultantInfoList) {
                    if (infos.getDiagnosisTime() != "" && infos.getDiagnosisTime() != null) {
                        infos.setDiagnosisTimeOne(infos.getDiagnosisTime().substring(0, 10));
                        infos.setDiagnosisTimeTwo(infos.getDiagnosisTime().substring(11, infos.getDiagnosisTime().length()));
                    }
                }
            }
            String tcName = "";
            String firstVisitDate = "";
            String sql3 = "select id, tc_name as tcName,first_visit_date as firstVisitDate from reservations_infosc a where id= '" + id + "' limit 0,1";
            ReservationsInfo reservationsInfo1 = sqlMapper.selectOne(sql3, ReservationsInfo.class);
            if (reservationsInfo1 != null) {
                tcName = reservationsInfo1.getTcName();
                firstVisitDate = reservationsInfo1.getFirstVisitDate();
            }
            ReservationsInfo reservationsInfo = this.reservationsService.selectOneById(id);
            if (reservationsInfo == null) {   //为空，直接上门没有进市场表查总表
                reservationsInfo = this.reservationsService.selectOneById2(id);
            }
            String arrsql = "select t.*,t2.abbreviation from reservations_info t left join dept t2 on t.arrival_store=t2.simplename where t.id='" + id + "' limit 0,1 ";
            CustomerInfo customerInfor = sqlMapper.selectOne(arrsql, CustomerInfo.class);
            CustomerInfo customerInfo = new CustomerInfo();
            if (customerInfor != null) {      //到店患者信息
                String custom = "select * from " + customerInfor.getAbbreviation() + "customer_info where reservations_id='" + id + "' order by arrival_time desc limit 0,1 ";
                customerInfo = sqlMapper.selectOne(custom, CustomerInfo.class);
                if (customerInfo == null) {
                    customerInfo = customerInfor;
                }
            }
            String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + id + "' ";
            List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
            String dealTables = "";
            String rechargeTables = "";
            String customerTables = "";
            for (int i = 0; i < abbreviationList.size(); i++) {
                String head = abbreviationList.get(i);
                if (i == abbreviationList.size() - 1) {
                    dealTables += "select * from " + head + "deal_info where reservations_id='" + id + "' ";
                    rechargeTables += "select document_number,deal_id,id2,recharge_people,operation_time,remarks from " + head + "recharge_info where reservations_id='" + id + "' ";
                    customerTables += "select customer_name as customerName, left(t.create_appointment_time,19) as createAppointmentTime,left(t.start_time,19) as startTime,t.id as id,t.remarks_reservation as remarksReservation," +
                            "t.reservations_id as reservationsId,t.doctor as doctor,t.field_tc as fieldTc,t.remarks as remarks,t.arrival_status as arrivalStatus," +
                            "t.archiving as archiving,t.arrival_store as arrivalStore,t.customer_status as customerStatus,t.status as status," +
                            "t.appointment_pay_service as appointmentPayService " +
                            "from " + head + "customer_info t where reservations_id='" + customerInfo.getReservationsId() + "' ";
                } else {
                    dealTables += "select * from " + head + "deal_info where reservations_id='" + id + "' union ";
                    rechargeTables += "select document_number,deal_id,id2,recharge_people,operation_time,remarks from " + head + "recharge_info where reservations_id='" + id + "' union ";
                    customerTables += "select customer_name as customerName, left(t.create_appointment_time,19) as createAppointmentTime,left(t.start_time,19) as startTime,t.id as id,t.remarks_reservation as remarksReservation," +
                            "t.reservations_id as reservationsId,t.doctor as doctor,t.field_tc as fieldTc,t.remarks as remarks,t.arrival_status as arrivalStatus," +
                            "t.archiving as archiving,t.arrival_store as arrivalStore,t.customer_status as customerStatus,t.status as status," +
                            "t.appointment_pay_service as appointmentPayService " +
                            "from " + head + "customer_info t where reservations_id='" + customerInfo.getReservationsId() + "' union ";
                }
            }
            //账单记录
            Double totalServiceActualPay = 0.00;
            Double totalArrears = 0.00;
            String custDealSql = "select t1.id2 as id2," +
                    "t1.reservations_id as reservationsId, " +
                    "t1.outpatient_name as outpatientName," +
                    "left(t1.operation_time,19) as operationTime," +
                    "t1.deal_status as dealStatus," +
                    "GROUP_CONCAT( distinct (t1.doctor)) as doctor,GROUP_CONCAT( distinct (t1.nurse)) as nurse," +
                    "t1.tc_name as tcName," +
                    "sum(t1.service_actual_pay+t1.arrears+t1.deduction_amount+t1.deposit_amount) AS foldingPay," +
                    "sum(t1.service_actual_pay) as serviceActualPay," +
                    "sum(t1.arrears) as arrears," +
                    "sum(t1.deduction_amount) as deductionAmount," +
                    "sum(t1.deposit_amount) as depositAmount," +
                    "t1.remarks as  remarks,(select distinct(r1.document_number) from (" + rechargeTables + ") r1 where r1.deal_id=t1.id group by r1.deal_id) as documentNumber " +
                    "from (" + dealTables + ") t1 where t1.deal_status='已结账'and t1.reservations_id = '" + id + "' " +
                    "group by t1.operation_time order by t1.operation_time desc";
            List<PayServicedInfo> custDealList = sqlMapper.selectList(custDealSql, PayServicedInfo.class);
            for (int i = 0; i < custDealList.size(); i++) {
                List<PayServicedInfo> dealList = getDealInfoList(custDealList.get(i).getOperationTime(), custDealList.get(i).getReservationsId());
                custDealList.get(i).setDealList(dealList);
                custDealList.get(i).setDocumentNumber(dealList.get(0).getDocumentNumber());
                totalServiceActualPay = totalServiceActualPay + custDealList.get(i).getServiceActualPay() + custDealList.get(i).getDepositAmount();
                totalArrears = totalArrears + custDealList.get(i).getArrears();
            }
            //就诊记录
            String sql11 = "select * from (" + customerTables + ") t order by t.startTime desc";
            List<CustomerInfo> customerInfoList = sqlMapper.selectList(sql11, CustomerInfo.class);
            for (CustomerInfo infos : customerInfoList) {
                infos.setOutpatientName(ShiroKit.getUser().getOutpatientName());
                if (infos.getStartTime() != "" && infos.getStartTime() != null) {
                    infos.setStartTimeOne(infos.getStartTime().substring(0, 10));
                    infos.setStartTimeTwo(infos.getStartTime().substring(11, infos.getStartTime().length()));
                }
            }
            //支付记录
            String payDealSql = "select t.outpatient_name as outpatientName,left(t.get_time,19) as getTime,t.is_do as isDo,t.tc_name as tcName,t.deal_status as dealStatus,t2.recharge_people " +
                    "as rechargePeople,sum(t.folding_pay) as foldingPay,sum(t.service_actual_pay) as serviceActualPay, sum(t.arrears) as arrears," +
                    "(sum(t.folding_pay)+sum(t.arrears)) as tkfoldingPay,sum(t.deduction_amount) AS deductionAmount,sum(t.deposit_amount) as depositAmount," +   //退款=应收+欠费
                    "t2.remarks as remarks from (" + dealTables + ") t left join (select b.id2 as id2, b.recharge_people as recharge_people, " +
                    "b.operation_time as operation_time,b.remarks as remarks from (" + rechargeTables + ") b group by b.operation_time) t2 on t.get_time = t2.operation_time  where " +
                    "t.reservations_id='" + id + "' and t.deal_status = '已结账' group by t.get_time order by t.get_time desc;";
            List<PayServicedInfo> payDealList = sqlMapper.selectList(payDealSql, PayServicedInfo.class);
            Double totalPayDealActualPay = 0.00;
            Double totalPayDealArrears = 0.00;
            for (int i = 0; i < payDealList.size(); i++) {
                totalPayDealActualPay = BigDecimal.valueOf(totalPayDealActualPay).add(BigDecimal.valueOf(payDealList.get(i).getServiceActualPay())).doubleValue();
                totalPayDealActualPay = BigDecimal.valueOf(totalPayDealActualPay).add(BigDecimal.valueOf(payDealList.get(i).getDepositAmount())).doubleValue();

                totalPayDealArrears = BigDecimal.valueOf(totalPayDealArrears).add(BigDecimal.valueOf(payDealList.get(i).getArrears())).doubleValue();
            }
            //欠费记录
            String arrearsDealSql = "select * from (select t.id2 as id2,t.reservations_id as reservationsId,t.customer_id as customerId," +
                    " t.deal_status as dealStatus,t.outpatient_name as outpatientName,t.tc_name as tcName,t.remarks as remarks,left(t.operation_time,19) as operationTime," +
                    " t.doctor as doctor,t.nurse as nurse,sum(t.deposit_amount) AS depositAmount,sum(t.deduction_amount) AS deductionAmount,sum(t.folding_pay) as foldingPay,sum(t.service_actual_pay)+sum(t.deposit_amount) as serviceActualPay,sum(t.arrears) as arrears,group_concat(distinct t1.pay_service_name) as payServiceName " +
                    " from (" + dealTables + ") t  left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"pay_service_info",0) + " t1 on t.pay_service_id=t1.id where t.deal_status='已结账' group by t.operation_time ) t1 " +
                    " where t1.reservationsId = '" + id + "' and t1.arrears >0 order by t1.operationTime desc;";
            List<PayServicedInfo> arrearsDealList = sqlMapper.selectList(arrearsDealSql, PayServicedInfo.class);
            Double totalArrearsAmount = 0.00;
            for (int i = 0; i < arrearsDealList.size(); i++) {
                totalArrearsAmount += arrearsDealList.get(i).getArrears();
            }
            if (ShiroKit.getUser().getRefereeShipInfoList() == null) {
                String getListSql = "select id,referee_ship as refereeShip from referee_ship_info order by num asc;";
                List<RefereeShipInfo> refereeShipList = sqlMapper.selectList(getListSql, RefereeShipInfo.class);
                ShiroKit.getUser().setRefereeShipInfoList(refereeShipList);
            }
            if (ShiroKit.getUser().getEconomicAbilityList() == null) {
                String getListSql = "select id, economic_ability from economic_ability_info order by num asc;";
                List<EconomicAbilityInfo> economicAbilityList = sqlMapper.selectList(getListSql, EconomicAbilityInfo.class);
                ShiroKit.getUser().setEconomicAbilityList(economicAbilityList);
            }
            if (ShiroKit.getUser().getDepartmentList() == null) {
                String getListSql = "select a.department as department from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"pay_service_grade_info",0) + " a where a.is_upper=1 group by a.department order by a.id;";
                List<DepartmentInfo> departmentList=sqlMapper.selectList(getListSql,DepartmentInfo.class);
                ShiroKit.getUser().setDepartmentList(departmentList);
            }
            if (ShiroKit.getUser().getCharactersList() == null) {
                String getListSql = "select id, characters from character_info order by num asc;";
                List<CharacterInfo> charactersList=sqlMapper.selectList(getListSql,CharacterInfo.class);
                ShiroKit.getUser().setCharactersList(charactersList);
            }
            if (ShiroKit.getUser().getOccupationInfoList() == null) {
                String getListSql = "select id, occupation from occupation_info where isshow=1 order by num asc;";
                List<OccupationInfo> occupationList = sqlMapper.selectList(getListSql, OccupationInfo.class);
                ShiroKit.getUser().setOccupationInfoList(occupationList);
            }
            if (ShiroKit.getUser().getGetMedium() == null) {
                List<Map<String, Object>> getMedium = getListService.getMedium();
                ShiroKit.getUser().setGetMedium(getMedium);
            }
            if (ShiroKit.getUser().getConsumptionHabitsList() == null) {
                String getListSql = "select id, consumption_habits from consumption_habits_info order by  num asc;";
                List<ConsumptionHabitsInfo> consumptionHabitsList = sqlMapper.selectList(getListSql, ConsumptionHabitsInfo.class);
                ShiroKit.getUser().setConsumptionHabitsList(consumptionHabitsList);
            }
            model.addAttribute("consumptionHabitsList", ShiroKit.getUser().getConsumptionHabitsList());
            model.addAttribute("refereeShipList", ShiroKit.getUser().getRefereeShipInfoList());
            model.addAttribute("economicAbilityList", ShiroKit.getUser().getEconomicAbilityList());
            model.addAttribute("departmentList",ShiroKit.getUser().getDepartmentList());
            model.addAttribute("charactersList", ShiroKit.getUser().getCharactersList());
            model.addAttribute("occupationList", ShiroKit.getUser().getOccupationInfoList());
            model.addAttribute("customerInfo", customerInfo);
            model.addAttribute("outpatientManagementList", ShiroKit.getSessionAttr("outpatientManagementList"));
            model.addAttribute("userName", ShiroKit.getUser().getAccount());
            model.addAttribute("roleName", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"RoleName",0));
            model.addAttribute("reservationsId", id);
            model.addAttribute("reservationsInfo", reservationsInfo);
            List<AddVisitedInfo> revisitRecordList = service.getRevisitRecordList(id);
            model.addAttribute("payDealList", payDealList);
            model.addAttribute("arrearsDealList", arrearsDealList);
            model.addAttribute("custDealList", custDealList);
            model.addAttribute("revisitRecordList", revisitRecordList);
            model.addAttribute("consultantInfoList", consultantInfoList);
            model.addAttribute("totalPayDealActualPay", totalPayDealActualPay);
            model.addAttribute("totalPayDealArrears", totalPayDealArrears);
            model.addAttribute("totalServiceActualPay", totalServiceActualPay);
            model.addAttribute("totalArrearsAmount", totalArrearsAmount);
            model.addAttribute("totalArrears", totalArrears);
            model.addAttribute("tcName", tcName);
            model.addAttribute("firstVisitDate", firstVisitDate);
            model.addAttribute("customerInfoList", customerInfoList);
            model.addAttribute("getMedium", ShiroKit.getUser().getGetMedium());
            return PREFIX + "reservationsReachs.html";
        } catch (Exception e) {
            return "/errorClose.html";
        }
    }

    public List<PayServicedInfo> getDealInfoList(String operationTime, String reservationsId) {
        if (ToolUtil.isEmpty(reservationsId) || ToolUtil.isEmpty(operationTime)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' and status=1 ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        String dealTables = "";
        String rechargeTables = "";
        for (int i = 0; i < abbreviationList.size(); i++) {
            String head = abbreviationList.get(i);
            if (i == abbreviationList.size() - 1) {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' ";
                rechargeTables += "select * from " + head + "recharge_info where reservations_id='" + reservationsId + "' ";
            } else {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' union ";
                rechargeTables += "select * from " + head + "recharge_info where reservations_id='" + reservationsId + "' union ";
            }
        }
        String sql = "select t1.id as id,t1.outpatient_name as outpatientName,left(t1.get_time,19) as getTime,t1.is_do as isDo,t1.deal_status as dealStatus," +
                "t1.doctor1 as doctor,t1.nurse1 as nurse,t1.order_discount as orderDiscount,t1.deductionAmount as deductionAmount,t1.remarks as remarks,t1.serviceActualPay as serviceActualPay," +
                "t2.recharge_people as rechargePeople,t2.checkout_method as checkoutMethod," +
                "t2.document_number as documentNumber,t1.tc_name as tcName,left(t1.operation_time,19) as operationTime from (select t.id2 as id,t.outpatient_name as outpatient_name," +
                "t.get_time as get_time,t.tc_name as tc_name,t.is_do as is_do,t.deal_status as deal_status,GROUP_CONCAT(DISTINCT t.doctor) AS doctor1,GROUP_CONCAT(DISTINCT t.nurse) AS nurse1,(sum(t.service_actual_pay)+sum(t.deposit_amount)) as serviceActualPay," +
                "t.order_discount as order_discount,sum(t.deduction_amount) as deductionAmount,t.remarks as remarks,t.operation_time as operation_time from (" + dealTables + ") t where t.operation_time='" + operationTime + "' and t.reservations_id " +
                "= '" + reservationsId + "' and t.deal_status='已结账' group by t.get_time) t1 left join " + "(select b.recharge_people as recharge_people," +
                "b.document_number as  document_number,group_concat(distinct b.checkout_method) as checkout_method, b.operation_time as operation_time from (" + rechargeTables + ") b " +
                "group by b.operation_time) t2 on t1.get_time = t2.operation_time order by t1.get_time desc";
        List<PayServicedInfo> payServiceList = sqlMapper.selectList(sql, PayServicedInfo.class);
        return payServiceList;
    }
}